package com.ar4j.bench.jdbc.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

import com.ar4j.bench.common.domain.CategoryWithProductsWithItems;
import com.ar4j.bench.common.domain.ICategory;
import com.ar4j.bench.common.domain.IProduct;
import com.ar4j.bench.common.domain.ProductWithItems;
import com.ar4j.bench.common.service.ICategoryService;
import com.ar4j.bench.jdbc.domain.Category;

/**
 * A category service implemented using spring's JdbcTemplate
 */
@Service
public class CategoryJdbcService implements InitializingBean, ICategoryService {
  private static final String CATEGORY_BY_ID = "SELECT ID, NAME, IMAGE, DESCRIPTION FROM CATEGORY WHERE ID = ?";
  private static final String DELETE_BY_ID = "DELETE FROM CATEGORY WHERE ID = ?";
  
  private static final CategoryMapper MAPPER = new CategoryMapper();
  
  @Autowired
  private DataSource dataSource;
  
  @Autowired
  private ProductJdbcService productService;
  
  private JdbcTemplate template;

  public Long addCategory(ICategory category) {
    KeyHolder holder = new GeneratedKeyHolder();
    template.update(new CategoryInsert(category), holder);
    return holder.getKey().longValue();
  }
  
  public ICategory findById(Long id) {
    List<ICategory> results = template.query(CATEGORY_BY_ID, new Object[] { id }, MAPPER);
    if(results.size() != 1) {
      throw new IncorrectResultSizeDataAccessException(1, results.size());
    }
    
    return results.get(0);
  }
  
  public void updateCategory(ICategory category) {
    template.update(new CategoryUpdate(category));
  }
  
  public void removeById(Long id) {
    template.update(DELETE_BY_ID, id);
  }
  
  public Long addCategoryWithProductsWithItems(CategoryWithProductsWithItems category) {
    Long categoryId = addCategory(category.getCategory());
    for(ProductWithItems product : category.getProducts()) {
      product.getProduct().setCategoryId(categoryId);
      productService.addProductWithItems(product);
    }
    return categoryId;
  }
  
  public CategoryWithProductsWithItems findCategoryWithProductsWithItemsById(Long id) {
    CategoryWithProductsWithItems out = new CategoryWithProductsWithItems();
    out.setCategory(findById(id));
    out.setProducts(productService.findProductWithItemsByCategoryId(id));
    return out;
  }
  
  public void updateCategoryWithProductsWithItems(CategoryWithProductsWithItems category) {
    updateCategory(category.getCategory());
    
    Map<Long, IProduct> fromDb = new HashMap<Long, IProduct>();
    for(IProduct product : productService.findByCategoryId(category.getCategory().getId())) {
      fromDb.put(product.getId(), product);
    }
    
    for(ProductWithItems product : category.getProducts()) {
      if(product.getProduct().getId() == null || !fromDb.containsKey(product.getProduct().getId())) {
        productService.addProductWithItems(product);
      } else {
        productService.updateProductWithItems(product);
        fromDb.remove(product.getProduct().getId());
      }
    }
    
    for(IProduct product : fromDb.values()) {
      productService.removeProductWithItemsById(product.getId());
    }
  }
  
  public void removeCategoryWithProductsWithItemsById(Long id) {
    for(IProduct product : productService.findByCategoryId(id)) {
      productService.removeProductWithItemsById(product.getId());
    }
    
    removeById(id);
  }

  @Override
  public void afterPropertiesSet() throws Exception {
    template = new JdbcTemplate(dataSource);
  }
  
  private static class CategoryInsert implements PreparedStatementCreator {
    private static final String SQL = "INSERT INTO CATEGORY(NAME, IMAGE, DESCRIPTION) VALUE (?, ?, ?)";
    
    private ICategory category;
    
    public CategoryInsert(ICategory category) {
      this.category = category;
    }

    @Override
    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
      PreparedStatement ps = connection.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);
      
      int nextParameter = 1;
      ps.setString(nextParameter++, category.getName());
      ps.setString(nextParameter++, category.getImage());
      ps.setString(nextParameter++, category.getDescription());
      
      return ps;
    }
  }
  
  public static class CategoryMapper implements RowMapper<ICategory> {

    @Override
    public ICategory mapRow(ResultSet rs, int rowNum) throws SQLException {
      ICategory out = new Category();
      out.setId(rs.getLong(1));
      out.setName(rs.getString(2));
      out.setImage(rs.getString(3));
      out.setDescription(rs.getString(4));
      return out;
    }
  }
  
  private static class CategoryUpdate implements PreparedStatementCreator {
    private static final String SQL = "UPDATE CATEGORY SET NAME = ?, IMAGE = ?, DESCRIPTION = ? WHERE ID = ?";
    
    private ICategory category;
    
    public CategoryUpdate(ICategory category) {
      this.category = category;
    }

    @Override
    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
      PreparedStatement ps = connection.prepareStatement(SQL);
      
      int nextParameter = 1;
      ps.setString(nextParameter++, category.getName());
      ps.setString(nextParameter++, category.getImage());
      ps.setString(nextParameter++, category.getDescription());
      ps.setLong(nextParameter++, category.getId());
      
      return ps;
    }
  }
}
